#！ /usr/bin/python3
# coding=UTF-8

import sqlite3

#创建数据库连接
def opendb():
    conn = sqlite3.connect('./mydb.db')
    cur = conn.execute('create table if not exists tongxinlu(usernum integer primary key, \
    username varchar(128), password varchar(128), address varchar(125), telnum varchar(128))')
    return cur, conn

#查询全部信息
def showalldb():
    print('------------------6的数据-----------------------')
    hel = opendb()
    cur = hel[1].cursor()
    cur.execute('select * from tongxinlu')
    res = cur.fetchall()
    for line in res:
        for h in line:
            print(h)
        print
    cur.close()

#输入信息
def into():
    usernum = input('请输入学号：')
    username = input('请输入姓名：')
    password = input('请输入密码：')
    address = input('请输入地址：')
    telnum = input('请输入电话：')
    return usernum, username, password, address, telnum 

#添加内容
def adddb():
    print('----------------------------添加数据-----------------------------')
    person = into()
    hel = opendb()
    hel[1].execute('insert into tongxinlu values(?,?,?,?,?)', person)
    hel[1].commit()
    print('---------------------------添加成功---------------------------------')
    showalldb()
    hel[1].close()

#删除数据
def deldb():
    print('------------------------------删除数据--------------------------------------')
    delchoice = input('请输入要删除的学号：')
    hel = opendb()
    hel[1].execute('delete from tongxinlu where usernum = ?', delchoice)
    hel[1].commit()
    print('-----------------------------删除成功----------------------------------')
    showalldb()
    hel[1].close()

#修改数据库内容
def alter():
    print('-------------------修改数据库--------------------------')
    
    hel = opendb()
    showalldb()
    change = input('请输入要修改的学号：')
    person = into()
    hel[1].execute('update tongxinlu set usernum=?, username=?, password=?, address=?, telnum=? \
        where usernum=?'+change, person)
    hel[1].commit()
    showalldb()
    hel[1].close()
#查询数据
def searchdb():
    print('-------------------------------欢迎查询数据库---------------------------')
    choice = input('请输入要查询的学号：')
    hel = opendb()
    cur = hel[1].cursor()
    cur.execute('select * from tongxinlu where usernum = '+ choice)
    hel[1].commit()
    print('-----------------------查询成功----------------------------')
    for row in cur:
        print(row)
    cur.close()
    hel[1].close()

#是否继续
def conti(a):
    choice = input('是否继续：（y or n）：')
    if choice == 'y' or choice == 'Y':
        a = 1
    else: 
        a = 0
    return a

if __name__ == '__main__':
    flag = 1
    while flag:
        print('-----------------欢迎使用数据库通讯录-----------------------')
        msg = """
        请执行以下操作：
        添加 1 ，
        删除 2 ，
        修改 3 ，
        查询 4
        """
        choice = input(msg)
        if choice == '1':
            adddb()
            flag=conti(flag)
        elif choice == '2':
            deldb()
            flag=conti(flag)
        elif choice == '3':
            alter()
            flag=conti(flag)
        elif choice == '4':
            searchdb()
            flag=conti(flag)
        else:
            print('输入错误，请重新输入')